USE [volador]
GO
/****** Object:  Trigger [dbo].[trg_SendDataToHttp]    Script Date: 2025/4/11 16:37:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 第二部分：创建触发器
ALTER TRIGGER [dbo].[trg_SendDataToHttp]
ON [dbo].[PlayRecordList]
AFTER INSERT, UPDATE
AS
BEGIN
    -- 声明变量
    DECLARE @httpObject INT;
    DECLARE @url NVARCHAR(255) = 'http://192.168.11.39:5000/broadcast'; -- 替换为实际的 API URL
    DECLARE @method NVARCHAR(10) = 'POST';
    DECLARE @headers NVARCHAR(255) = 'Content-Type: application/xml';
    DECLARE @body NVARCHAR(MAX);
    DECLARE @status INT;
    DECLARE @response NVARCHAR(MAX);

    -- 获取插入的数据或更新的PRL_STATUS字段
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        -- 插入操作，直接获取数据
        SELECT @body = (
            SELECT 
                pl.PRL_EVN_TYPE as 'prlEvnType',
                pl.PRL_EVN_SOURCE as 'prlEvnSource',
                pl.PRL_AREACODE as 'prlAreacode',
                pl.PRL_STARTTIME as 'prlStarttime',
                pl.PRL_ENDTIME as 'prlEndtime',
                pl.PRL_STATUS as 'prlStatus',
                CASE
                    WHEN (pl.prl_areaDetail IS NULL OR pl.prl_areaDetail = '') THEN pl.PRL_AREACODE
                    ELSE pl.prl_areaDetail
                END as 'areaname'
            FROM inserted pl
            FOR XML PATH('item'), ROOT('items')
        );
    END
    ELSE IF EXISTS (
        SELECT 1 
        FROM inserted i
        JOIN deleted d ON i.PRL_ID = d.PRL_ID
        WHERE i.PRL_STATUS <> d.PRL_STATUS
    )
    BEGIN
        -- 更新操作且PRL_STATUS字段有变化
        SELECT @body = (
            SELECT 
                pl.PRL_EVN_TYPE as 'prlEvnType',
                pl.PRL_EVN_SOURCE as 'prlEvnSource',
                pl.PRL_AREACODE as 'prlAreacode',
                pl.PRL_STARTTIME as 'prlStarttime',
                pl.PRL_ENDTIME as 'prlEndtime',
                pl.PRL_STATUS as 'prlStatus',
                CASE
                    WHEN (pl.prl_areaDetail IS NULL OR pl.prl_areaDetail = '') THEN pl.PRL_AREACODE
                    ELSE pl.prl_areaDetail
                END as 'areaname'
            FROM inserted pl
            FOR XML PATH('item'), ROOT('items')
        );
    END
    ELSE
    BEGIN
        -- 更新操作但PRL_STATUS字段无变化，不发送请求
        RETURN;
    END
    
    -- 直接使用XML格式数据
    -- 无需转换，直接使用@body中的XML内容

    -- 创建 HTTP 对象
    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @httpObject OUT;

    -- 打开 HTTP 请求
    EXEC sp_OAMethod @httpObject, 'open', NULL, @method, @url, false;

    -- 设置请求头
    EXEC sp_OAMethod @httpObject, 'setRequestHeader', NULL, 'Content-Type', 'application/xml';

    -- 发送请求
    EXEC sp_OAMethod @httpObject, 'send', NULL, @body;

    -- 释放 HTTP 对象
    EXEC sp_OADestroy @httpObject;


END;